👪 Excel Bestand
Het auto-categorizatie stuk van ons huidige Excel sheet is mogelijk handig voor gezinnen die net beginnen met een eigen sheet. Deze pagina is dan ook een goodie. Het is niet direct gerelateerd aan de "manier" omdat "de manier" in elk tool gedaan kan worden.Tabbladen
Het basis Excel sheet is simpel bestaat uit een aantal sheets:
-
In het eerste sheet copy en paste je simpelweg de bank-exports van al je transacties onder elkaar. In ons spreadsheet
hebben we er rond de 17.000 in staan vanaf 2005. Hiernaast staan een extra kolommen zoals:
- Een kolom waarin je een term/category/post/label kunt plaatsen bij die specifieke transactie
- Die kolom wordt automatisch gevuld door het categorizatie script maar... als je hem zelf al ingevuld hebt dan zal je eigen keuze niet overschreven worden.
- Als de auto categorizatie een andere waarde vindt dan die jezelf ingevuld hebt dan zal deze in een kolom ernaast geplaatst worden, handig als dubbelcheck
- Een kolom die ons verteld wie de transactie gedaan heeft aan de hand van het pasnummer (als het een betaling was met een pasnummer)
- Kolommen met subcategorieen als het label puntjes bevat, puntjes worden opgevat als hierarchie
-
Sheet waar je regels in kunt noteren bijvoorbeeld: rekeningnummer X is label "donald duck"
uitstapje naar de toekomst:
Dit is de huidige opzet, die prima werkt. Maar het kan nog beter :) Het zou handiger zijn als iedereen met een spreadsheet zijn regels kan delen. Dan hoeft maar 1 keer iemand de Donald Duck te categorizeren en bij alle andere mensen wordt deze dan ook automatisch gecategorizeeerd. Dit vereist wel het hebben van gelijke taxomomie of het hebben van meerdere taxonomieen, waarvan een de A-B-C=D taxonomie is.
In de echte wereld zijn er systemen waar functies gestandaardiseerd zijn. In XACML bijvoorbeeld is er een standaard lijst met functies zoals "string-equal". Deze verwacht dan een attribuut, die altijd bestaat uit een "categorie" met een eigenschap, als ook een waarde. Als we dit spreadsheet gaan uitbreiden dan zal het die kanten op gaan. Want dat betekent dat we een stapje omhoog gaan en 1 categorie hebben: transactie, die dan meerdere eigenschappen heeft. Die eigenschappen verbind je dan aan waardes en de specifieke instantie van een functie sla je dan op in json of xml zodat we alle functies die mensen dan willen delen kunnen gaan verzamelen op een plek. Dat is handig want dan hoef je ze zelf niet meer toe te voegen. In theorie zouden alle budget software systemen dan van dezelfde pool gebruik kunnen maken. En we leveragen dan op open OASIS standaarden voor de uitwisseling van deze benoemde functies. Dat vereist een aparte pagina met de regels, dat staat nog op TODO :)
Een tweede verbetering hierin is dan om die json of xml van die benoemde functie "donald duck" uit te breiden met nog meer gestandaardiseerde informatie zoals metadata (adres, link, opzeggen, forum, prijsverloop over jaren) maar ook met vrije smarte regels die de input zijn voor een gezin om keuzes te maken. Ook hier is XACML een idee generator: niet alleen data kan gestandaardiseerd worden uitgewisseld maar ook regels.
We breiden dus in de tweede verbetering die donaldduck.json van "benoemde functie" naar "iets met veel meer data dan alleen herkenningsregel" via open standaarden.
Dat vereist wel dat je minimaal de A-B-C-D taxonomie gebruikt
En ook dat kan een derde verbetering hebben: als mensen eenmaal die auto regels gebruiken en delen. Dan kan je het zo gek maken als je zelf wilt. Je kunt dan functies toevoegen die niet alleen categorizeren maar "alles" kunnen: als je een rekening hebt van een specifiek vakantiehuisje dan zou iemand een regel kunnen maken die daar weer op acteert e.d. en als je een pinbon hebt van de MacDonalds in Groningen dan zou je ook ervaringen kunnen delen via die functie voor die specifieke MacDonals OF een functie kunnen toevoegen die iets doet met alle macdonalds. Dat kan zo gek worden als mensen zelf verzinnen
Dit is allemaal vrij simpel dus we verwachten dit ergens op korte termijn te releasen.
- Een sheet waarin alle labels staan, zodat je ze handig uit een pulldown menu kunt selecteren. Op diezelfde sheet staat achter die kolom het "totaal" en dan per jaar het totaal. Zo fungeert die slide als lijst van je labels en tegelijkertijd als een snel overzicht.
- (optioneel) Via
Categorizatie script
"In de beginne" zal het script niet alles categorizeren omdat je nog niet genoeg regels hebt gemaakt. Dus met en met kun je regels toevoegen door telkens degene die overblijven in het regelsheet te plaatsen. Het is niet heel veel werk maar ook niet weinig werk.
idee: Als we in GitHub regels zouden delen dan zou dit voor iedereen minder werk zijn.
#If False Then
Dim Range, Accounts, BudgetLine, Category, Color, RowContent
#End If
Option Explicit
Function Col_Letter(lngCol As Long) As String
Dim vArr
vArr = Split(Cells(1, lngCol).Address(True, False), "$")
Col_Letter = vArr(0)
End Function
Sub OptimizeCode_Begin()
Application.ScreenUpdating = False
EventState = Application.EnableEvents
Application.EnableEvents = False
CalcState = Application.Calculation
Application.Calculation = xlCalculationManual
PageBreakState = ActiveSheet.DisplayPageBreaks
ActiveSheet.DisplayPageBreaks = False
End Sub
Sub OptimizeCode_End()
ActiveSheet.DisplayPageBreaks = PageBreakState
Application.Calculation = CalcState
Application.EnableEvents = EventState
Application.ScreenUpdating = True
End Sub
'
' Main sub to start
'
Sub Categorize()
Dim importsheetRow As Long
Dim RuleRow As Long
Dim RuleColumn As Long
Dim RuleColumnLetter As String
Dim RuleImportLookupColumn As String
Dim RuleComparisonRule As String
Dim IsEmptyCell As Boolean
Dim RuleComparisonValue As Range
Dim ImportValue As Range
Dim RuleIsTrue As Boolean
Dim ImportCategoryColumn As String
Dim CurrentCategory As String
Dim FutureCategory As String
Dim FutureCategoryColor As Long
Dim ShouldWriteCategory As Boolean
Dim AmountRuleMatches As Integer
Call OptimizeCode_Begin
For importsheetRow = 2 To ThisWorkbook.Sheets("Import").Range("A" & Rows.Count).End(xlUp).row
ImportCategoryColumn = ThisWorkbook.Sheets("Rules").Range("A1").Text
CurrentCategory = ThisWorkbook.Sheets("Import").Range(ImportCategoryColumn + CStr(importsheetRow)).Text
If True Or (CurrentCategory = "") Then
For RuleRow = 4 To ThisWorkbook.Sheets("Rules").Range("A" & Rows.Count).End(xlUp).row
RuleIsTrue = True
AmountRuleMatches = 0
For RuleColumn = 3 To ThisWorkbook.Sheets("Rules").Cells(1, Columns.Count).End(xlToLeft).Column
RuleColumnLetter = Col_Letter(RuleColumn)
RuleImportLookupColumn = ThisWorkbook.Sheets("Rules").Range(RuleColumnLetter + "1").Text
RuleComparisonRule = ThisWorkbook.Sheets("Rules").Range(RuleColumnLetter + "3").Text
If Not IsEmpty(ThisWorkbook.Sheets("Rules").Range(RuleColumnLetter + CStr(RuleRow))) Then
Set RuleComparisonValue = ThisWorkbook.Sheets("Rules").Range(RuleColumnLetter + CStr(RuleRow))
Set ImportValue = ThisWorkbook.Sheets("Import").Range(RuleImportLookupColumn + CStr(importsheetRow))
If (RuleComparisonRule = "Smaller") Then
If Not (RuleComparisonValue.Value < ImportValue.Value) Then
RuleIsTrue = False
Exit For
Else
AmountRuleMatches = AmountRuleMatches + 1
End If
End If
If (RuleComparisonRule = "Larger") Then
If Not (RuleComparisonValue.Value > ImportValue.Value) Then
RuleIsTrue = False
Exit For
Else
AmountRuleMatches = AmountRuleMatches + 1
End If
End If
If (RuleComparisonRule = "Equal") Then
If (IsNumeric(RuleImportLookupColumn)) Then
If Not (RuleComparisonValue.Value = ImportValue.Value) Then
RuleIsTrue = False
Exit For
Else
AmountRuleMatches = AmountRuleMatches + 1
End If
Else
If Not (RuleComparisonValue.Text = ImportValue.Text) Then
RuleIsTrue = False
Exit For
Else
AmountRuleMatches = AmountRuleMatches + 1
End If
End If
End If
If (RuleComparisonRule = "Contains") Then
If (ImportValue.Text = "") Or (Not (ImportValue.Text Like "*" + RuleComparisonValue.Text + "*")) Then
RuleIsTrue = False
Exit For
Else
AmountRuleMatches = AmountRuleMatches + 1
End If
End If
If (RuleComparisonRule = "StartsWith") Then
If (ImportValue.Text = "") Or (Not Left(ImportValue.Text, Len(RuleComparisonValue)) = RuleComparisonValue) Then
RuleIsTrue = False
Exit For
Else
AmountRuleMatches = AmountRuleMatches + 1
End If
End If
If (RuleComparisonRule = "Empty") Then
If ImportValue.Text <> "" Then
RuleIsTrue = False
Exit For
Else
AmountRuleMatches = AmountRuleMatches + 1
End If
End If
End If
Next RuleColumn
If RuleIsTrue = True And AmountRuleMatches > 0 Then
FutureCategory = ThisWorkbook.Sheets("Rules").Range("A" + CStr(RuleRow)).Text
If CurrentCategory = "" Then
ThisWorkbook.Sheets("Import").Range(ImportCategoryColumn + CStr(importsheetRow)).Value = FutureCategory
ThisWorkbook.Sheets("Import").Range(ImportCategoryColumn + CStr(importsheetRow)).Interior.ColorIndex = 4
ElseIf CurrentCategory <> FutureCategory Then
ThisWorkbook.Sheets("Import").Range(ImportCategoryColumn + CStr(importsheetRow)).Interior.ColorIndex = 3
ThisWorkbook.Sheets("Import").Range("AB" + CStr(importsheetRow)).Value = FutureCategory
ElseIf CurrentCategory = FutureCategory Then
ThisWorkbook.Sheets("Import").Range(ImportCategoryColumn + CStr(importsheetRow)).Interior.ColorIndex = 50
Else
ThisWorkbook.Sheets("Import").Range(ImportCategoryColumn + CStr(importsheetRow)).Interior.ColorIndex = 9
End If
Exit For
End If
Next RuleRow
End If
Next importsheetRow
Call OptimizeCode_End
MsgBox "Klaar!"
End Sub